using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
//using System.Windows.Forms;

/*
	The way-too-late module change-log
	02/07/2012: implemented non-null clause in table scripter.
				sorted out the reserved word pkey issue
	09/07/2012: Make all-upper table and field names lower in Pascal Case routine.
*/
namespace SqlServerDriver
{
	class SqlServerScripter
	{
		/*
		 *  Build a script for a given table from metadata.
		 * The metadata table contains the follwing columns.
		 * 0 = Field name
		 * 1 = Type
		 * 2 = length
		 * 3 = Nulls allowed flag
		 * 4 = Calculated field flag (nb: calculated fields are not supported)
		 * 5 = Tags for additional info eg PK, AUTO etc.
		 * 6 = Index or other constraint names
		 * 7 = Other information eg details of default values (nb: default values not supported)
		 * 
		 * Not done:
		 * - defaults
		 * - not for Replication
		 * - foreign keys
		 * - indexes
		 *   1. Check Extra and Extra detail.
		 *   2. Where nth , item is IDXn index name is nth Extra detail.
		 *   3. create hash of arrays on names - see array stuff for adding elements.
		 */

		String sourceTableName;
		SqlTableSchema schema;
		/*
				public static String MakeCopyScript(String strConnectionString, String SourceTableName, String TargetTableName)
				{
					SqlServerScripter engine = new SqlServerScripter(strConnectionString, SourceTableName, TargetTableName);
					return engine.Script();
				}
		*/

		public SqlServerScripter(String tableName, SqlTableSchema schema)
		{
			this.sourceTableName = tableName;
			this.schema = schema;
		}

		public String ScriptCreateTable(bool forStaging)
		{
			String script = "";
			bool hasSchema = (this.sourceTableName.IndexOf(".") != -1); // breaking when the table name has a . in it.
			// MAkes the script look scruffy.
			//if (hasSchema)
			//    script += "-- Remove or replace the schema name below if it does not exist in the database\n";
			ArrayList scriptRows = new ArrayList();  // holds target sql 
			String[] tableParts = Helper.ParseTableName(this.sourceTableName);
			// now build the script. 
			scriptRows.Add(String.Format("create table {0} (", Helper.SafeTableName(this.sourceTableName)));
			bool isPkey = false;      // flag to detect a Primary key field
			String strConn = ",";      // for comma logic
			// loop through each fiels
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				isPkey = (isPkey | fi.IsPKey); // capture Pkey
				scriptRows.Add(this.FieldSqlFromFieldInfo(fi, forStaging) + strConn);
			}
			// add the PKey definition if required
			if (isPkey)
				this.AddPrimaryKey(scriptRows, tableParts[0]); // use only the table name for constraints.

			this.StripFinalComma(scriptRows); // may or may not be necessary
			scriptRows.Add(")");

			script += String.Join("\r\n", (String[])scriptRows.ToArray(typeof(String)));

			return script;
		}

// Old version. Slighly to complicated with the schema handling
//        public String ScriptCreateTable(bool forStaging)
//        {
//            String Script = "";

//            ArrayList ScriptRows = new ArrayList();  // holds target sql 
//            String[] tableParts = Helper.ParseTableName(this.m_SourceTableName);
//            // now build the script. 
//            ScriptRows.Add(String.Format("create table {0} (", tableParts[0]));
//            bool bIsPkey = false;      // flag to detect a Primary key field
//            String strConn = ",";      // for comma logic
//            // loop through each fiels
//            for (int idx = 0; idx < m_Schema.Count; ++idx)
//            {
//                DbView.FieldInfo fi = m_Schema[idx];
//                bIsPkey = (bIsPkey | fi.IsPKey); // capture Pkey
//                ScriptRows.Add(FieldSqlFromFieldInfo(fi, forStaging) + strConn);
//            }
//            // add the PKey definition if required
//            if (bIsPkey)
//                AddPrimaryKey(ScriptRows, tableParts[0]);

//            StripFinalComma(ScriptRows); // may or may not be necessary
//            ScriptRows.Add(")");
//            // now some schema stuff
//            if (tableParts.GetLength(0) > 1) // schema in play
//            {
//                String fixSchema = String.Format(@"
//-- Transfer the table to the source schema.
//-- Notes: 
//-- remove below a. if you wan't to transfer the table to schema {1}
//--           or b. if the target database is SQL 2000 or 97
//if exists(select * from sys.schemas where name = '{1}')
//	ALTER SCHEMA {1} TRANSFER dbo.{0}", tableParts[0], tableParts[1]);
//                ScriptRows.Add(fixSchema);
//            }

//            Script = String.Join("\r\n", (String[])ScriptRows.ToArray(typeof(String)));

//            return Script;
////        }
		
		public String ScriptCreateTable()
		{
			return this.ScriptCreateTable(false);
		}

		private void StripFinalComma(ArrayList scriptRows)
		{
			int lastLineIdx = scriptRows.Count - 1;
			if (lastLineIdx < 0) return;   // nope. Array is empty
			String lastLine = scriptRows[lastLineIdx].ToString();
			if (lastLine.Length == 0) return; // nope. last line is blank
			if (lastLine[lastLine.Length - 1] == ',') // last line terminates with , (assume no spare spaces on the end)
			{
				lastLine = lastLine.Substring(0, lastLine.Length - 1);
				scriptRows[lastLineIdx] = lastLine;
			}
		}

		// todo identity
		// data with ' in
		// options for []
		public String ScriptData(DataTable table)
		{
			// define 2 sanity constraints
			// abandon if the script runs above this
			const int MAX_SCRIPT_LENGTH = 5000000;
			// copy to clipboard if above this size
			const int MAX_DISPLAY_LENGTH = 500000;

			StringBuilder script = new StringBuilder("set nocount on; \r\n");
			int scriptLength = 0; // sanity 
			script.Append("delete from " + table.TableName + ";\r\n");
			//
			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
				// identidy insert
				script.Append(SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, table.TableName, ""));

			foreach (DataRow dr in table.Rows)
			{
				String recordScript = this.ScriptRow(dr);
				script.Append(recordScript);
				scriptLength += recordScript.Length;
				if (scriptLength > MAX_SCRIPT_LENGTH)
					throw new Exception("Data set too large to script (Sorry!)");
			}
			if (idField != null)
			{
				// identidy insert
				script.Append(SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed,table.TableName, idField.Name));
			}

			if (scriptLength > MAX_DISPLAY_LENGTH)
			{
				System.Windows.Forms.Clipboard.SetText(script.ToString());
				return "*** The script is too large to display. It's been dumped into the clipboard instead ***";
			}

			return script.ToString();
		}

		private String ScriptRow( DataRow dr)
		{
			DataTable table = dr.Table;
			String scriptRow = String.Format("insert into {0} (%1) values (%2);\r\n", table.TableName);
			String fields = "";
			String values = "";
			for (int idx = 0; idx < dr.ItemArray.GetLength(0); ++idx)
			{
				String field = table.Columns[idx].ColumnName;
				DbView.FieldInfo fi = this.schema[field];
				if (!fi.IsCalc) // not if calculated
				{
					if (fields.Length > 0)
						fields += ", ";
					fields += MakeSafeName(table.Columns[idx].ColumnName);
					if (values.Length > 0)
						values += ", ";
					String data = dr[idx].ToString();
					if (data.Length == 0)
						values += "NULL";
					else
					{
						if (fi.Type == "D")
							values += "'" + MakeDateInsertField(dr[idx]) + "'";
						else if (fi.Type == "B")
							values += MakeBitInsertField(dr[idx]);
						else if (fi.Type == "C")
						{
							String prefix = (fi.IsUnicode) ? "N" : "";
							values += prefix + "'" + dr[idx].ToString().Replace("\'", "\'\'") + "'";
						}
						else
							values += dr[idx].ToString();
					}
				}
			}
			String recordScript = scriptRow.Replace("%1", fields).Replace("%2", values);
			return recordScript;
		}

		// Cannot allow this functionality if the password is encrypted
		// You may script it, but not run it.
		public String ScriptRowsetRefresh(String tableName, DbView.ConnectionInfo sourceConn, SqlScriptingHelper scriptHelper)
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
if object_id('tempdb..{0}') is not null drop table {0}
select dt.* into {0} from OPENROWSET('SQLNCLI','{1}';'{2}';'{3}', {4}.dbo.{5}) AS dt
            ", tableName, sourceConn.Server, sourceConn.UID, sourceConn.PWD, sourceConn.Database, tableName);

			script += String.Format(@"
delete from [{0}] where not exists
       (select * from [{1}] where {2})
            ", tableName, tableName, scriptHelper.ScriptKeyCompare());
			/*
update BANK set 
	BANK_NAME = BANK_20081017_103911.BANK_NAME
 from BANK_20081017_103911
where BANK.BANK_ID = BANK_20081017_103911.BANK_ID
 and CHECKSUM(BANK.BANK_ID) <> CHECKSUM(BANK_20081017_103911.BANK_ID)
			*/
			script += String.Format(@"
update [{0}] set {2}
  from [{1}]
 where {3} 
   and {4}
            ", tableName, tableName, scriptHelper.ScriptUpdate(),
					   scriptHelper.ScriptKeyCompare(),
					   scriptHelper.ScriptCheckSumCompare(false));

			DbView.FieldInfo idField = scriptHelper.Schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, tableName,"");
			}
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}] 
 where not exists (select * from [{0}] where {3}) 
            ", tableName, tableName, scriptHelper.ScriptAllButCalcFields(), scriptHelper.ScriptKeyCompare());

			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, tableName, idField.Name);
			}
			return script;
		}

		public String ScriptContentCopy(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			if (scriptHelper.Schema.PKeyFields.GetLength(0) > 0)
				return this.ScriptContentCopyPkey(sourceTableName, targetTableName, scriptHelper);
			else
				return this.ScriptContentCopyNoPkey(sourceTableName, targetTableName, scriptHelper);
		}

		public String ScriptContentCopyPkey(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			String script = "/*  Script to copy content */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/

			script += String.Format(@"
delete from [{0}] where not exists
       (select * from [{1}] where {2})
            ", targetTableName, sourceTableName, scriptHelper.ScriptKeyCompare(targetTableName));

			script += String.Format(@"
update [{0}] set {2}
  from [{1}]
 where {3} 
   and {4}
            ", targetTableName, sourceTableName, scriptHelper.ScriptUpdate(targetTableName),
					   scriptHelper.ScriptKeyCompare(targetTableName),
					   scriptHelper.ScriptCheckSumCompare(targetTableName, false));

			DbView.FieldInfo idField = scriptHelper.Schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, targetTableName,"");
			}
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}] 
 where not exists (select * from [{0}] where {3}) 
            ", targetTableName, sourceTableName, scriptHelper.ScriptAllButCalcFields(), scriptHelper.ScriptKeyCompare(targetTableName));

			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, targetTableName, idField.Name);
			}
			return script;
		}

		public String ScriptContentCopyNoPkey(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			String script = "/*  Script to copy content */\n";
			script += String.Format("delete from [{0}];\n", targetTableName);
			DbView.FieldInfo idField = scriptHelper.Schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, targetTableName,"");
			}
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}]
", targetTableName, sourceTableName, scriptHelper.ScriptAllButCalcFields());

			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, targetTableName, idField.Name);
			}
			return script;
		}
		
		internal string ScriptCompareTableData()
		{
			SqlScriptingHelper helper = new SqlScriptingHelper(this.sourceTableName, this.schema);
			string srcTableAlias = "s"; // 
			string tgtTableAlias = "t"; // 
			string selectFrom = helper.ScriptKeyList(srcTableAlias);
			string keyComp = helper.ScriptKeyCompare(tgtTableAlias);
			// have to modify the KeyCompare to replace the source name with the alias name
			keyComp = keyComp.Replace(Helper.SafeTableName(this.sourceTableName), Helper.SafeTableName(srcTableAlias));
			string template = @"
select {0},
{6}
  from {1} as {2}
  join {3} as {4} on {5}
  where 1=0 -- make this 1=1 to see all rows{7}
";
			return string.Format(template, selectFrom, Helper.SafeTableName(this.sourceTableName), srcTableAlias,
											Helper.SafeTableName(this.sourceTableName), tgtTableAlias, keyComp,
											helper.ScriptCompareFields(srcTableAlias, tgtTableAlias),
										    helper.ScriptRowNotEqual(srcTableAlias, tgtTableAlias));
		
		}

		// when creating a copy of one table from another you need to create new
		// default constraint names. This routine first tries to replace  
		// any substrings of the old table name with the new table name.
		// if this fails ie. the old table name is not in the constraint name 
		// then as a fallback it suffixes the new table name to the end of the
		// constraint name.
		String FixText(String target, String sourceTable, String targetTable)
		{
			// regex to find the source table name in 
			String newName = target.Replace(sourceTable, targetTable);
			if (newName == target)
				newName = String.Format("{0}_{1}", newName, targetTable);
			return newName;
		}

		void AddPrimaryKey(ArrayList scriptRows, String tableName)
		{
			int pkeyCount = 0;
			ArrayList keyFieldList = new ArrayList();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					keyFieldList.Add(fi.SafeName);
					++pkeyCount;
				}
			}
			// the boilerplate is hacked from table scripts from other sources not from the 
			// table itself.
			if (pkeyCount > 0)
			{
				scriptRows.Add(String.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED (", tableName));
				scriptRows.Add(String.Join(",\r\n", (String[])keyFieldList.ToArray(typeof(String))));
				scriptRows.Add(") "); 
//				ScriptRows.Add(")  WITH  FILLFACTOR = 90  ON [PRIMARY]"); // I don't fully understand this so lets remove it and not pretend.
			}
		}

		/*
		 *  Detect and script defaults incrementing fields.
		 */
		String GetDefaultInfo(DbView.FieldInfo fi)
		{
			String defaultInfo = "";
			if (fi.HasDefault)
			{
				String defaultName = String.Format("DF_{0}_{1}", this.sourceTableName.Replace(".", "_"), fi.Name);
				defaultInfo = String.Format(" CONSTRAINT {0} DEFAULT {1}", defaultName, fi.ConstraintDefinition);
			}
			return defaultInfo;
		}

		String GetComputedInfo(DbView.FieldInfo fi)
		{
			String info = "";
			if (fi.IsCalc)
			{
				if (String.IsNullOrEmpty(fi.ConstraintDefinition))
					throw new Exception(String.Format("Field {0} is computed, but the computation is not available\nPerhaps the current connection does not have sufficient permissions.", fi.Name));
				info = fi.ConstraintDefinition;
			}
			return info;
		}

		String GetIdentityInfo(DbView.FieldInfo fi)
		{
			String identInfo = "";
			if (fi.IsAuto)
			{
				identInfo = " IDENTITY";
				identInfo += String.Format("({0},{1})", fi.AutoSeed, fi.AutoInc);
			}
			return identInfo;
		}

		/*
		 *  Script a field
		 */

		String FieldSqlFromFieldInfo(DbView.FieldInfo fi, bool forStaging)
		{
			String sql = "";
			String nonNull = (fi.AllowsNulls) ? "" : " NOT NULL ";
			String fieldType = fi.SqlType;
			// adjust to add length qualifiers. Hm. Floats not done yet
			if (fieldType.ToUpper().IndexOf("CHAR") != -1)
			{
				String fieldSize = (fi.Length > 0) ? fi.Length.ToString() :
									(fi.Length == -1) ? "MAX" : fi.Length.ToString();
				fieldType = String.Format("{0}({1})", fieldType, fieldSize);
			}
			else if (RequiresDp(fi))
			{
				fieldType = String.Format("{0}({1},{2})", fieldType, fi.Length, fi.Dp);
			}
			
			String identityInfo = this.GetIdentityInfo(fi);
			String defaultInfo = this.GetDefaultInfo(fi);
			// If the table is for staging (specialised) then don't enforce the computed, non-null or default
			// (that will be done in the update)
			if (forStaging)
			{
				nonNull = "";
				defaultInfo = "";
			}
			if (fi.IsCalc && !forStaging)
				sql = String.Format("{0} AS {1}", fi.SafeName, this.GetComputedInfo(fi));
			else
				sql = String.Format("{0} {1}{2}{3}{4}", fi.SafeName, fieldType, identityInfo, nonNull, defaultInfo);

			return sql;
		}

		static String MakeSafeName(String raw)
		{
			return String.Format("[{0}]", raw);
		}

		static String MakeDateInsertField(object o)
		{
			// assume 
			DateTime dt = (DateTime)o;
			String s = dt.ToString("yyyyMMdd HH:mm:ss");
			return s;
		}

		static String MakeBitInsertField(object o)
		{
			// assume 
			bool b = (bool)o;
			return (b) ? "1" : "0";
		}

		static bool RequiresDp(DbView.FieldInfo fi)
		{
			return (fi.SqlType.ToUpper() == "DECIMAL" ||
					fi.SqlType.ToUpper() == "NUMERIC") ? true : false;
		}
	
		static string MakeNullProxy(DbView.SchemaField field)
		{
			return (field.Type == "S") ? "`" : // choose an unlikely character.
			(field.Type == "I") ? "-8989" :  // random very unusual value.
			(field.Type == "J") ? "-89898989" :  // random very unusual value (bigint)
			(field.Type == "N") ? "-8989.9898" :  // random very unusual value.
			(field.Type == "X") ? "239" :     // will be inaccurate, but no choice
			(field.Type == "D") ? "19700102"
							   : "0"; // this should suit all
		}
		static public long CountTableRows(String connSource, String tableSource)
		{
			SqlConnection cnn = new SqlConnection(connSource);
			SqlCommand commandRowCount = new SqlCommand(String.Format("SELECT count(*) FROM {0}", tableSource), cnn);
			cnn.Open();
			long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
			cnn.Close();
			cnn = null;
			return countStart;
		}

		public void CopyTable(String connSource, String connTarget, String tableSource, String tableTarget, bool copyContent, SqlRowsCopiedEventHandler progress)
		{
			// before release add a check that the table does not exist 
			// may have different name, but has same schema
			SqlServerScripter newTableBuilder = new SqlServerScripter(tableTarget, this.schema);
			String script = newTableBuilder.ScriptCreateTable();
			try
			{
				DataSet dset;
				Helper.GenericMakeDset(connTarget, script, tableTarget, out dset);
				dset = null;
				if (copyContent == false) return; // job done.
			}
			catch (Exception e)
			{
				String extraMessage = (tableSource.IndexOf(".") > 0) ? "\n- or the schema does not exist in the target database" : "";
				string messageTemplate = 
@"Error on copy attempt:
Probably either:
- Target table exists or
- Target database is readonly{1}
Possibly the table contains a user-defined type missing in the target.

Check the error detail below:

{0}";
				String message = String.Format(messageTemplate, e.Message, extraMessage);
				throw new Exception(message);
			}

			try
			{
				SqlConnection cnn = new SqlConnection(connSource);
				// Getting source data
				SqlCommand cmd = new SqlCommand(String.Format("SELECT * FROM {0}", tableSource), cnn);
				cnn.Open();
				SqlDataReader rdr = cmd.ExecuteReader();
				// Initializing an SqlBulkCopy object
				SqlBulkCopy sbc = new SqlBulkCopy(connTarget, SqlBulkCopyOptions.KeepIdentity);
				// Copying data to destination
				sbc.DestinationTableName = tableTarget;
				sbc.BatchSize = 1000;    // 1000 rows at a time
				sbc.NotifyAfter = 1000;  // generate an event
				if (progress != null)
					sbc.SqlRowsCopied += progress;
				sbc.WriteToServer(rdr);
				// Closing connection and the others
				sbc.Close();
				rdr.Close();
				cnn.Close();
			}
			catch (SqlException e)
			{
				// may not work if connection fails
				String message = String.Format("Table Copy Error:\n {0}", e.Message);
				throw new Exception(message);
				// MessageBox.Show(Message, "Copy Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}


	}

	// static methods 
	internal class FieldScripter
	{
		internal static String ScriptField(DbView.FieldInfo fi, string tableName)
		{
			String sql = "";
			String nonNull = (fi.AllowsNulls) ? "" : " NOT NULL ";
			String fieldType = fi.SqlType;
			
			// adjust to add length qualifiers. Hm. Floats not done yet
			if (fieldType.ToUpper().IndexOf("CHAR") != -1)
			{
				String fieldSize = (fi.Length > 0) ? fi.Length.ToString() :
									(fi.Length == -1) ? "MAX" : fi.Length.ToString();
				fieldType = String.Format("{0}({1})", fieldType, fieldSize);
			}
			else if (RequiresDp(fi))
			{
				fieldType = String.Format("{0}({1},{2})", fieldType, fi.Length, fi.Dp);
			}
			
			String identityInfo = GetIdentityInfo(fi);
			String defaultInfo = GetDefaultInfo(fi, tableName);
			if (fi.IsCalc)
				sql = String.Format("[{0}] AS {1}", fi.Name, GetComputedInfo(fi));
			else
				sql = String.Format("[{0}] {1}{2}{3}{4}", fi.Name, fieldType, identityInfo, nonNull, defaultInfo);

			return sql;
		}

		static bool RequiresDp(DbView.FieldInfo fi)
		{
			return (fi.SqlType.ToUpper() == "DECIMAL" ||
					fi.SqlType.ToUpper() == "NUMERIC") ? true : false;
		}

		static String GetDefaultInfo(DbView.FieldInfo fi, string tableName)
		{
			String defaultInfo = "";
			if (fi.HasDefault)
			{
				String defaultName = MakeDefaultName(fi, tableName);
				defaultInfo = String.Format(" CONSTRAINT {0} DEFAULT {1}", defaultName, fi.ConstraintDefinition);
			}
			return defaultInfo;
		}

		public static String MakeDefaultName(DbView.FieldInfo fi, string tableName)
		{
			return (fi.HasDefault) ? String.Format("DF_{0}_{1}", tableName.Replace(".","_"), fi.Name) : "";
		}

		static String GetComputedInfo(DbView.FieldInfo fi)
		{
			String info = "";
			if (fi.IsCalc)
			{
				info = fi.ConstraintDefinition;
			}
			return info;
		}

		static String GetIdentityInfo(DbView.FieldInfo fi)
		{
			String identInfo = "";
			if (fi.IsAuto)
			{
				identInfo = " IDENTITY";
				identInfo += String.Format("({0},{1})", fi.AutoSeed, fi.AutoInc);
			}
			return identInfo;
		}
	}

	internal class FkScripter
	{
		public DataTable KeyInfoTable { get; set; }
		public int TableCol { get; set; }
		public int NameCol { get; set; }
		public int FieldCol { get; set; }
		public int PkTableCol { get; set; }
		public int PkCol { get; set; }
		public int DisabledCol { get; set; }
		public int ReplicationCol { get; set; }
		public int DeleteActionCol { get; set; }
		public int UpdateActionCol { get; set; }

		public void ConfigureTable(DataTable dt, int tableCol, int nameCol, int fieldCol, int pkeyTableCol, int pkeyCol)
		{
			this.KeyInfoTable = dt;
			this.TableCol = tableCol;
			this.NameCol = nameCol;
			this.FieldCol = fieldCol;
			this.PkTableCol = pkeyTableCol;
			this.PkCol = pkeyCol;
			this.DisabledCol = -1;
			this.ReplicationCol = -1;
			this.DeleteActionCol = -1;
			this.UpdateActionCol = -1;
		}

		public String ScriptKeys(String tgtTableName, bool allTables, bool dropOnly)
		{
			String script = ""; ;
			String prevTableName = "";
			for (int idx = 0; idx < this.KeyInfoTable.Rows.Count; ++idx)
			{
				DataRow dr = this.KeyInfoTable.Rows[idx];
				String fkeyName = dr[this.NameCol].ToString();
				String tableName = dr[this.TableCol].ToString();
				String pkeyTable = dr[this.PkTableCol].ToString();
				String fkeyField = this.GetFkFields(dr, fkeyName, idx);
				String pkeyField = this.GetPkFields(dr, fkeyName, idx);
				if (tableName == tgtTableName || allTables == true)
				{
					if (tableName != prevTableName)
					{
						script += "\n-- *** Scripting " + Helper.SafeTableName(tableName) + " ***";
						prevTableName = tableName;
					}
					script += String.Format("\n-- drop constraint if it exists already" +
										   "\nif object_id('{0}') is not null \n\talter table {1} drop constraint {0}", fkeyName, tableName);
					String comment = "-- ";      // to disable
					if (!dropOnly) comment = ""; // only of drop only mode
					script += (this.IsDisabled(dr)) ? String.Format("\n-- Add without enforcing the constraint" +
																	"\n{5}Alter table {0} with nocheck \n{5}\tadd constraint {1} \n{5}\tforeign key({2})\n{5}\treferences {3}({4})",
																	Helper.SafeTableName(tableName), fkeyName, Helper.SafeSqlField(fkeyField), Helper.SafeTableName(pkeyTable), Helper.SafeSqlField(pkeyField), comment)
													: String.Format("\n -- uncomment text in the script below to add the FK without enforcing integrity (not recommended)" +
																	"\n{5}Alter table {0} /* with nocheck */ \n{5}\tadd constraint {1} \n{5}\tforeign key({2})\n{5}\treferences {3}({4})",
																	Helper.SafeTableName(tableName), fkeyName, Helper.SafeSqlField(fkeyField), Helper.SafeTableName(pkeyTable), Helper.SafeSqlField(pkeyField), comment);
					if (this.MakeUpdateAction(dr).Length > 0) script += String.Format("\n{1}\t{0}", this.MakeUpdateAction(dr), comment);
					if (this.MakeDeleteAction(dr).Length > 0)script += String.Format("\n{1}\t{0}", this.MakeDeleteAction(dr), comment);
					// add possibly commented out not for replication clause
					comment = "-- "; // to discable
					if (this.IsNotForReplication(dr) && !dropOnly) comment = ""; // enable it.
					script += String.Format(" {2}not for replication", tableName, fkeyName, comment);

					comment = "--";
					if (this.IsDisabled(dr) && !dropOnly) comment = ""; // enable it.
					script += String.Format("\n{2}Alter table {0} nocheck constraint {1}\n", Helper.SafeTableName(tableName), Helper.SafeSqlField(fkeyName), comment);
				}
				idx += CalculateSkipRows(fkeyField);
			}
			return script;
		}

		private String MakeUpdateAction(DataRow dr)
		{
			if (this.UpdateActionCol < 0) return "";
			String updateAction = dr[this.UpdateActionCol].ToString();
			switch (updateAction[0])
			{
				case 'S':
					return "on update set null";
				case 'C':
					return "on update cascade";
				default:
					return "";
			}
		}

		private String MakeDeleteAction(DataRow dr )
		{
			if (this.DeleteActionCol < 0) return "";
			String deleteAction = dr[this.DeleteActionCol].ToString();
			switch (deleteAction[0])
			{
				case 'S':
					return "on delete set null";
				case 'C':
					return "on delete cascade";
				default:
					return "";
			}
		}

		private static int CalculateSkipRows(String fkeyField)
		{
			// skip rows for multi-part keys
			int idx = 0;
			int cidx = fkeyField.IndexOf(',');
			while (cidx != -1)
			{
				++idx;
				cidx = fkeyField.IndexOf(',', cidx + 1);
			}
			return idx;
		}

		private string GetFkFields(DataRow dr, String fkeyName, int idx)
		{
			String fields = dr[this.FieldCol].ToString();
			while (true)
			{
				++idx;
				if (idx >= this.KeyInfoTable.Rows.Count) return fields;
				if (this.KeyInfoTable.Rows[idx][this.NameCol].ToString() != fkeyName) return fields;
				fields += "," + this.KeyInfoTable.Rows[idx][this.FieldCol].ToString();
			}
		}

		private string GetPkFields(DataRow dr, String fkeyName, int idx)
		{
			String fields = dr[this.PkCol].ToString();
			while (true)
			{
				++idx;
				if (idx >= this.KeyInfoTable.Rows.Count) return fields;
				if (this.KeyInfoTable.Rows[idx][this.NameCol].ToString() != fkeyName) return fields;
				fields += "," + this.KeyInfoTable.Rows[idx][this.PkCol].ToString();
			}
		}

		bool IsDisabled(DataRow dr)
		{
			if (this.DisabledCol == -1) return false; // not configured. Assume na.
			String data = dr[this.DisabledCol].ToString();
			if (data.ToUpper() == "Y" || data == "1") // two possibilities
				return true;
			return false;
		}

		bool IsNotForReplication(DataRow dr)
		{
			if (this.ReplicationCol == -1) return false; // not configured. Assume na.
			String data = dr[this.ReplicationCol].ToString();
			if (data.ToUpper() == "Y" || data == "1") // two possibilities
				return true;
			return false;
		}
	}
}
